Q2 流失会员
/**建表

 */
create table ads.ads_member_lost_cnt(
    cnt bigint,
    dt string
)comment '流失会员'

insert into table ads.ads_member_lost_cnt
select 
count(*) cnt, '2020-08-24' as dt
from
dws.dws_member_add_day mem
left join
(select distinct `device_id` from dws.dws_member_start_day where dt<='2020-08-24' and dt>=date_add('2020-08-24',-30)) act
on mem.device_id=act.device_id
where act.device_id is null;